﻿Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Calendar1_SelectionChanged(sender As Object, e As EventArgs) Handles Calendar1.SelectionChanged
        txtDepart.Text = Calendar1.SelectedDate.ToString("dd/MM/yyyy")
    End Sub

    Private Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        FillRoute()
        Button3.PostBackUrl = "SelectDepartingFlight.aspx"
    End Sub

    'Fills Routes DropDownList
    Sub FillRoute()
        '*** DataSet From DataSet ***'
        Dim ds As DataSet
        ds = CreateDataSet("SELECT * FROM route")

        Dim i As Integer
        If DropDownList1.Items.Count = 0 Then

            For i = 0 To ds.Tables.Count + 1
                DropDownList1.Items.Add(ds.Tables(0).Rows(i)(1).ToString() + " - " + ds.Tables(0).Rows(i)(2).ToString())
                DropDownList1.Items.Item(i).Value = ds.Tables(0).Rows(i)(0).ToString()
            Next
        End If

    End Sub

    '*** Gets a DataSet from the routes table ***'
    Function CreateDataSet(srtSql As String) As DataSet
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand
        Dim dtAdapter As New MySql.Data.MySqlClient.MySqlDataAdapter

        Dim ds As New DataSet
        Dim strConnString

        strConnString = "Server=localhost;User Id=goldair_dapp; Password=geckodev; Database=goldair_db; Pooling=false"


        objConn.ConnectionString = strConnString
        With objCmd
            .Connection = objConn
            .CommandText = srtSql
            .CommandType = CommandType.Text
        End With
        dtAdapter.SelectCommand = objCmd

        Try
            dtAdapter.Fill(ds, "route")
        Catch ex As Exception

        End Try


        dtAdapter = Nothing
        objConn.Close()
        objConn = Nothing

        Return ds   '*** Return DataSet ***'

    End Function

    'Look for a flight on the current date and route
    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim route As String
        Dim qDate As String

        route = DropDownList1.SelectedItem.Value.ToString()
        Table2.Visible = True
        Table3.Visible = False

        qDate = txtDepart.Text

        If qDate = "" Or route = "" Then
            MsgBox("Please Select a Route and Date", vbInformation)
        Else
            '  MsgBox(route + "*" + qDate)

            Dim ds As DataSet
            ds = CreateDataSet("SELECT id as 'Flight Number',departure as 'Departure Time',flight_time as 'Time On Flight ',cost as 'Cost' FROM flight where route_id=" & route & " and DATE_FORMAT(departure , '%d/%m/%Y')='" & qDate & "'")


            If ds.Tables(0).Rows.Count <= 0 Then
                MsgBox("There is no Flight for this Date and Destination", vbExclamation)

            Else

                For i = 0 To ds.Tables.Count - 1

                    'MsgBox(ds.Tables(0).Rows(i)(1).ToString() & ds.Tables(0).Rows(i)(5).ToString() & ds.Tables(0).Rows(i)(7).ToString())
                    Table2.Visible = False
                    Table3.Visible = True

                    GridView1.Visible = True
                    GridView1.DataSource = ds
                    GridView1.DataBind()

                   
                Next

            End If


            'clear dataset
            ds.Clear()
            ds = Nothing
            txtDepart.Text = Nothing

        End If



    End Sub

    'Assign the selected data of the grid to the html elements
    Protected Sub GridView1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles GridView1.SelectedIndexChanged

        txtFlight1.Value = GridView1.SelectedRow.Cells(1).Text
        txtDepartureTime.Value = GridView1.SelectedRow.Cells(2).Text
        txtFlightTime.Value = GridView1.SelectedRow.Cells(3).Text
        txtCost.Value = GridView1.SelectedRow.Cells(4).Text
        txtRoute.Value = DropDownList1.SelectedItem.Text


    End Sub


End Class